﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;
using System.IO;
using System.Resources;
using System.Reflection;
using System.Diagnostics;
using HuberRace.BusinessLayer;
using System.Data.SqlClient;
using System.Configuration;


namespace HuberRace
{
    public partial class Main : Form
    {
        private Stopwatch stopWatch = new Stopwatch();
        private bool timerOn = false;
        private TimeSpan calibrateTime = new TimeSpan();
        StreamWriter swFinishTimes;

        ///TODO This should not be public. It is right now as it's needed by
        ///OpenRace dialog.
        public SqlConnection cn;

        // Stores place, bib number, and times
        private DataTable resultTable = new DataTable();
        private SqlDataAdapter daFinishOrder = new SqlDataAdapter();

        /// <summary>
        /// Initialize the main window. Restore window size and load text instructions
        /// stored in ResourceManager.
        /// </summary>
        public Main()
        {
            InitializeComponent();

            restoreWindowSize();

            // Setup various display elements for the form.
            var rm = new ResourceManager("HuberRace.Wizard", Assembly.GetExecutingAssembly());
            guidance.Text = rm.GetString("FirstPage");

            distanceUnits.SelectedIndex = 0;
        }

        /// <summary>
        /// Opens a race. Sets the program to use this as its race and
        /// binds the UI to elements of the race.
        /// </summary>
        /// <param name="raceId"></param>
        public void OpenRace(int raceId)
        {
            var newRace = new Race();
            Program.CurrentRace = newRace;
            newRace.RaceId = raceId;

            var sc = new SqlCommand(@"
                SELECT name 
                FROM Races 
                WHERE raceId = @raceId", cn);
            sc.Parameters.AddWithValue("@raceId", raceId);
            newRace.Name = (string)sc.ExecuteScalar();
            AddRaceMessage("Race " + newRace.Name + " opened!");
            Text = "Huber Race: " + newRace.Name;

            if (swFinishTimes != null)
                swFinishTimes.Close();

            swFinishTimes = new StreamWriter("FinishTimes_" + Program.CurrentRace.RaceId + ".txt", true);
            
            populateFinishOrderGrid();
            populateParticipantsGrid();
        }

        /// <summary>
        /// Populate the "finish order" grid that is used to enter sequence
        /// of bib finishers.
        /// </summary>
        private void populateFinishOrderGrid()
        {
            int raceId = Program.CurrentRace.RaceId;

            var cmd = new SqlCommand(@"
                SELECT 
                    p.raceName,
                    t.place as place, 
                    t.spotBib as spotBib,
                    ISNULL(CONVERT(VARCHAR(50), t.time, 108),'') as time, 
                    b.bib as bib, 
                    name, gender, age
                    --,age, , AG,
                    --GenderPlace, [AG Place], TeamName
                FROM TimeOrderPlace t 
                LEFT JOIN BibOrderPlace b ON
                    b.place = t.place AND
                    t.raceId = b.raceId
                LEFT JOIN Participants p ON
                    ISNULL(b.bib,t.spotBib) = p.bib
                WHERE t.raceId = @raceId 
                ORDER BY PLACE ASC", cn);
            cmd.Parameters.AddWithValue("@raceId", raceId);
            daFinishOrder.SelectCommand = cmd;

            var dt = new DataTable();
            daFinishOrder.Fill(dt);

            finishOrderGrid.Columns["race"].DataPropertyName = "raceName";
            finishOrderGrid.Columns["place"].DataPropertyName = "place";
            finishOrderGrid.Columns["time"].DataPropertyName = "time";
            finishOrderGrid.Columns["bib"].DataPropertyName = "bib";
            finishOrderGrid.Columns["name"].DataPropertyName = "name";
            finishOrderGrid.Columns["spotBib"].DataPropertyName = "spotBib";
            finishOrderGrid.Columns["age"].DataPropertyName = "age";
            finishOrderGrid.Columns["gender"].DataPropertyName = "gender";

            finishOrderGrid.DataSource = dt;
        }

        #region Methods for handling participants grid

        SqlDataAdapter da; // DataAdapter for mapping the Participants database table with the Participants grid
        SqlCommandBuilder sb; // Used to give us INSERT, UPDATE, and DELETE commands for the Participants database table

        /// <summary>
        /// Populate "Participants Grid" which displays info on participants screen
        /// </summary>
        private void populateParticipantsGrid()
        {
            var dt = new DataTable();
            da = new SqlDataAdapter(@"
                SELECT participantId, bib, name, gender, age, DOB, raceName, team, regForFullSeries, isUSATF, city, state, email, address1, address2, zip, phone
                FROM Participants
                ORDER BY bib", cn);
            sb = new SqlCommandBuilder(da); // This automatically gets us update, insert, and delete statements.

            da.RowUpdated += delegate(object sender, SqlRowUpdatedEventArgs e)
            {
                // If an insert statement has occured, get the identity column 
                // and store it in the DataGridView. 
                if (e.Command.CommandText.Contains("INSERT INTO"))
                {
                    var cmd = new SqlCommand("SELECT @@IDENTITY", cn);
                    int participantId;
                    if (!Int32.TryParse(cmd.ExecuteScalar().ToString(), out participantId))
                    {
                        throw new Exception(
                            "Invalid primary key returned from insert. Can not refresh Participants grid with inserted row primary key.");
                    }

                    var dgvDt = (DataTable)participantsGrid.DataSource;
                    var dr = dgvDt.Select("bib = " + e.Row["bib"].ToString());
                    dr[dr.Length - 1]["participantId"] = participantId;
                }
            };

            da.Fill(dt);

            participantsGrid.DataSource = dt;
            participantsGrid.Columns[0].Visible = false;

        }

        private int saveParticipantsGrid()
        {
            try
            {
                var dt = (DataTable)participantsGrid.DataSource;
                da.Update(dt);
                displaySaveMessage();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occured updating the database. Check that the data you input is valid. Error message: " + ex.Message, "Error updating database", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                return 0;
            }
            return 1;
        }

        private void displaySaveMessage()
        {
            // Display a message on UI that the grid has been saved.
            participantGridMessage.Text = String.Format("Data saved. {0} rows saved.", participantsGrid.Rows.Count);

            // Cause the message displayed to disappear in one second.
            var t = new System.Windows.Forms.Timer();
            t.Interval = 1 * 1000; // Interval is stored in milliseconds.
            t.Tick += clearSaveMessageTime_Elapsed;
            t.Start();
        }

        private void refreshParticipants_Click(object sender, EventArgs e)
        {
            populateParticipantsGrid();
        }

        private void clearSaveMessageTime_Elapsed(object sender, EventArgs e)
        {
            participantGridMessage.Text = "";
            var t = sender as System.Windows.Forms.Timer;
            t.Stop();
        }

        private void participantsGrid_CellValidating(object sender,
            DataGridViewCellValidatingEventArgs e)
        {
            if (String.IsNullOrEmpty(e.FormattedValue.ToString()))
                return;

            string headerText = participantsGrid.Columns[e.ColumnIndex].HeaderText;
            if (headerText == "bib")
            {
                // Ensure bib is integer
                int bib;
                bool isBibValidInt = int.TryParse(e.FormattedValue.ToString(), out bib);

                // Give error if bib number is already in use
                if (isBibValidInt == false)
                {
                    participantsGrid.Rows[e.RowIndex].ErrorText =
                        "Bib number is not valid";
                    MessageBox.Show("Bib number is not a valid number. If you wish to not input this row, press Ok to this dialogue then press the Esc key.", "Bib Number Invalid", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                    e.Cancel = true;
                }

                // Ensure the bib is unique
                var dgvDt = (DataTable)participantsGrid.DataSource;
                var participantId = participantsGrid.Rows[e.RowIndex].Cells["participantId"].Value.ToString();
                String selectString = String.Empty;
                if (!String.IsNullOrEmpty(participantId))
                {
                    selectString = "AND participantId <> " + participantId;
                } 
                if (dgvDt.Select("bib = " + e.FormattedValue.ToString() + selectString).Length > 0)
                {
                    participantsGrid.Rows[e.RowIndex].ErrorText =
                        "Bib number in use";
                    MessageBox.Show("Bib number has been assigned to another runner..", "Bib Number In Use", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                    e.Cancel = true;
                }
                
            }
        }
        
        private void participantsGrid_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            // Clear the row error in case the user presses ESC.   
            participantsGrid.Rows[e.RowIndex].ErrorText = String.Empty;
        }



        private void participantsGrid_RowValidating(object sender, DataGridViewCellCancelEventArgs e)
        {
            // If all cells of row are empty, do not perform any further validation
            bool allCellsEmpty = participantsGrid.Rows[e.RowIndex].Cells.Cast<DataGridViewCell>().All(c => c.Value == null || String.IsNullOrEmpty(c.Value.ToString()));
            if (allCellsEmpty)
                return;


            // If row does not contain a bib number, give error
            if (participantsGrid.Rows[e.RowIndex].Cells["bib"].Value == null || String.IsNullOrEmpty(participantsGrid.Rows[e.RowIndex].Cells["bib"].Value.ToString()))
            {
                participantsGrid.Rows[e.RowIndex].ErrorText =
                    "Must enter bib number first";
                MessageBox.Show("Must enter a bib number. Before you can add this participant the bib number must be entered.", "Enter Bib Number", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                e.Cancel = true;
                return;
            }
        }
        
        
        private void participantsGrid_RowValidated(object sender, DataGridViewCellEventArgs e)
        {


            if (cbAutoSaveParticipantsGrid.Checked)
            {
                // Save the grid everytime a participant moves to a new row.
                // saveParticipantsGrid will return 0 if the save fails.
                if (saveParticipantsGrid() == 0)
                {
                    // Most likely we shouldn't do an auto-refresh. Instead we should just disable
                    // auto-saving until fixed.
                    MessageBox.Show("An error occured updating the database. Auto save of the grid is being disabled. We recommend you immediately identify error before doing any other tasks and once fixed re-enable Auto Save by checking the 'Auto Save' checkbox", "Auto Saving Disabled", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);

                    cbAutoSaveParticipantsGrid.Checked = false;
                }
            }
        }

        private void saveParticipants_Click(object sender, EventArgs e)
        {

            int currentRowIndex = participantsGrid.CurrentRow.Index;
            
            ///TODO Better validation of DataGridView
            // If no error saving participants, refresh grid. 
            // Otherwise display error message.
            if (saveParticipantsGrid() == 1)
            {
                // We use to refresh the grid after saving. There's no need to do this and
                // it'd changge the cursor, so I've disabled this.                
                //populateParticipantsGrid();
                
                // Restore CurrentCell to first cell of cursor being edited.
                ///TODO look for more graceful way to handle saving then refreshing
                ///particiapnts grid. Reseting the grid causes sortorder to be lost in 
                ///addition to current place
                ///
                ///TODO at the very least remember cell we were editing
                //participantsGrid.CurrentCell = participantsGrid.Rows[currentRowIndex].Cells[1];  
            }
        }

        #endregion 

        #region Private methods that handle window rendering

        private void restoreWindowSize()
        {
            this.FormClosing += new System.Windows.Forms.FormClosingEventHandler(this.Main_FormClosing);
            this.Resize += new System.EventHandler(this.Main_Resize);
            this.StartPosition = FormStartPosition.Manual;
            this.Location = Properties.Settings.Default.Location;
            this.WindowState = Properties.Settings.Default.State;
            if (this.WindowState == FormWindowState.Normal) this.Size = Properties.Settings.Default.Size;
        }

        private void Main_FormClosing(object sender, FormClosingEventArgs e) {
            Properties.Settings.Default.Save();
        }
        private void Main_Resize(object sender, EventArgs e) {
            Properties.Settings.Default.State = this.WindowState;
            if (this.WindowState == FormWindowState.Normal) Properties.Settings.Default.Size = this.Size;
        }
        #endregion

        /// <summary>
        /// Print in the Race Configuration tab a message; typically 
        /// program status.
        /// </summary>
        /// <param name="msg"></param>
        public void ProgramStatus(string msg)
        {
            guidance.Text = msg;
        }

        public void InputBibsMessage(string msg)
        {
            ProgramStatus(msg);
        }

        public void ResultsMessage(string msg)
        {
            ProgramStatus(msg);
        }

        public void AddRaceMessage(string msg)
        {
            ProgramStatus(msg);
        }


        private void newRaceToolStripMenuItem_Click(object sender, EventArgs e)
        {
            createDataTableForBibEntry();

            Form AddRace = new AddRace(this);
            AddRace.Show();
        }

        private void createDataTableForBibEntry()
        {
           
            var AutoNumberColumn = new DataColumn();
            AutoNumberColumn.ColumnName = "Place";
            AutoNumberColumn.DataType = typeof(int);
            AutoNumberColumn.AutoIncrement = true;
            AutoNumberColumn.AutoIncrementSeed = 1;
            AutoNumberColumn.AutoIncrementStep = 1;
            resultTable.Columns.Add(AutoNumberColumn);

            var Bib = new DataColumn();
            Bib.ColumnName = "Bib Number";
            Bib.DataType = typeof(int);
            resultTable.Columns.Add(Bib);


            var Time = new DataColumn();
            Time.ColumnName = "Time";
            Time.DataType = typeof(string);
            resultTable.Columns.Add(Time);


            finishOrderGrid.Columns[0].DataPropertyName = "Place";
            finishOrderGrid.Columns[1].DataPropertyName = "Bib Number";
            finishOrderGrid.Columns[2].DataPropertyName = "Time";

            finishOrderGrid.DataSource = resultTable;

        }

        private void Main_Load(object sender, EventArgs e)
        {
            recordTimeLabel.Text = "";
        }

        private void recordTimeBtn_Click(object sender, EventArgs e)
        {
            recordTime();
        }

        private void startStopBtn_Click(object sender, EventArgs e)
        {
            if (!timerOn)
            {
                TimeSpan.TryParse(clock.Text.ToString(), out calibrateTime);
                stopWatch.Reset();
                stopWatch.Start();
                refreshClockTimer.Start();
                timerOn = true;

                ///TODO. May consider not resetting this to 1 everytime clock is started.
                ///A user may intentionally want to stop/start the clock in a race to
                ///get it sync'd
                particpantNum = 1;

                startStopBtn.Text = "Stop";
                recordTimeBtn.Enabled = true;
                timeBibEntry.SelectAll();
                timeBibEntry.Focus();
            }
            else
            {
                if (MessageBox.Show("Are you sure you want to stop the timer?", "Confirm Timer Stop", MessageBoxButtons.YesNo) == DialogResult.No)
                {
                    return;
                }

                stopWatch.Stop();
                refreshClockTimer.Stop();
                timerOn = false;
                startStopBtn.Text = "Start";
                recordTimeBtn.Enabled = false;
            }
        }


        private void timeBibEntry_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                recordTime();

                timeBibEntry.Text = "000";
                timeBibEntry.SelectAll();
                timeBibEntry.Focus();
            }
           
        }

        private void refreshStopWatchTimer_Tick(object sender, EventArgs e)
        {
            if (timerOn)
            {
                TimeSpan ts = clockTime();

                string elapsedTime = 
                        String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                            ts.Hours, ts.Minutes, ts.Seconds,
                            ts.Milliseconds / 10);

                clock.Text = elapsedTime;
            }
        }

        private int particpantNum;

        private void recordTime()
        {
            Race r = Program.CurrentRace;

            int bibNumber;
            try
            {
                bibNumber = Int32.Parse(timeBibEntry.Text);
            }
            catch
            {
                bibNumber = 0;
            }

            TimeSpan ts = clockTime();

            string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                ts.Hours, ts.Minutes, ts.Seconds,
                ts.Milliseconds / 10);

            string writeToFile;
            string bibNumberTxt;
            bool insertSpotBib;
            if (bibNumber > 0)
            {
                insertSpotBib = true;
                bibNumberTxt = "Bib " + String.Format("{0:000}", bibNumber) + " - ";
                writeToFile = elapsedTime + "," + bibNumber;
            }
            else
            {
                insertSpotBib = false;
                bibNumberTxt = "";
                writeToFile = elapsedTime;
            }

            swFinishTimes.WriteLine(writeToFile);
            swFinishTimes.Flush();

            recordTimeLabel.Text = "Time # " + particpantNum + " Recored: " + bibNumberTxt + elapsedTime;
            particpantNum++;
            timeBibEntry.Text = "000";

            
            // Insert time into db
            try
            {
                var sc = new SqlCommand(@"
                    INSERT INTO TimeOrder 
                        (raceId, time, spotBib)
                    VALUES 
                        (@raceId, @elapsedTime, @bibToInsert)", cn);

                sc.Parameters.AddWithValue("@raceId", r.RaceId);
                sc.Parameters.AddWithValue("@elapsedTime ", elapsedTime);

                if (insertSpotBib)
                {
                    sc.Parameters.AddWithValue("@bibToInsert", bibNumber);
                }
                else
                {
                    sc.Parameters.AddWithValue("@bibToInsert", DBNull.Value);
                }

                sc.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                recordTimeLabel.Text += "\n DB ERROR: " + ex.Message + "\nTime recorded on file system only.";
                return;
            }

        }

        private TimeSpan clockTime()
        {
            TimeSpan ts = stopWatch.Elapsed + calibrateTime;
            return ts;
        }

        private void saveBibs_Click(object sender, EventArgs e)
        {
            string bibColumn = "bib";
            var dt = (DataTable)finishOrderGrid.DataSource;

            int p = 1;
            var cmd = new SqlCommand();
            cmd.Connection = cn;

            var swBib = new StreamWriter("Bibs_"+Program.CurrentRace.RaceId+".txt", true);
            foreach (DataRow r in dt.Rows)
            {
                if (r[bibColumn].ToString() == "")
                    continue; 

                swBib.WriteLine(r[bibColumn]);
            }
            swBib.Close();

            SqlTransaction tran = cn.BeginTransaction();
            cmd.Transaction = tran;

            try
            {
                cmd.CommandText = @"
                    DELETE FROM BibOrderPlace WHERE raceId = " + Program.CurrentRace.RaceId;
                cmd.ExecuteNonQuery();

                foreach (DataRow r in dt.Rows)
                {
                    if ((r[bibColumn]).ToString() == "")
                        continue;

                    string sql = String.Format(@"
                        INSERT INTO BibOrderPlace 
                            (raceId, place, bib) 
                        VALUES 
                            ({0}, {1}, {2})", Program.CurrentRace.RaceId, p, r[bibColumn].ToString());
                    
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    p++;
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    tran.Rollback();
                }
                catch
                {
                }

                MessageBox.Show("Could not write to db. Bibs still written to file system. Error: " + ex.Message);
            }

            swBib.Close();
        }


        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if ((Program.CurrentRace == null) && (mainTabs.SelectedTab != raceConfig))
            {
                MessageBox.Show("You must first create the race on the Race Configuraton tab.");
                mainTabs.SelectedTab = raceConfig;
            }

        }

        private void addRace_Click(object sender, EventArgs e)
        {
            if (raceName.Text == "")
            {
                MessageBox.Show("You must enter a name for your race.");
                raceName.Select();
                return;
            }

            if (raceDistance.Text == "")
            {
                MessageBox.Show("You must enter a distance for your race.");
                raceName.Select();
                return;
            }            


            var newRace = new Race();
            newRace.Name = raceName.Text;
            newRace.Times = new List<string>();
            Program.CurrentRace = newRace;

            createDataTableForBibEntry();

            AddRaceMessage("Race " + newRace.Name + " created.\nNext use the Participants tab to load participants.");
        }

        private void import_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            //Form ImportParticipants1 = new Import_Participants_1(this);
            //ImportParticipants1.Show();
            //return;
        }

        private void toolStripMenuItem2_Click(object sender, EventArgs e)
        {
            var dg = (DataGridViewCellEventArgs)e;
            return;
        }


        private void openRaceButtton_Click(object sender, EventArgs e)
        {
            openRaceForm();
        }

        private void openRaceForm()
        {
            Form OpenRace = new OpenRace(this);
            OpenRace.Show();
        }

        public void EstablishSQLConnection()
        {
            string cnStr;
            try
            {
                ///TODO We should allow this to be set by the UI.
                cnStr = ConfigurationManager.ConnectionStrings["RaceScore"].ConnectionString;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not read connection string in configuraton file. Error: " + ex.Message);
                return;
            }

            /// TODO: Address concern  SqlConnect is opened, but times out.
            // If the connection closes due to inactivity while .NET automatically open it?
            // Should we use a wrapper to cn to ensure it's opened when it's closed or
            // can we trust it's always open?
            // May need to set MinPoolSize=0 in connection string.
            cn = new SqlConnection(cnStr);
            cn.ConnectionString = cnStr;

            try
            {
                cn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not connect to database: " + cnStr + "\n" + "Error: " + ex.Message.ToString());
                return;
            }
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            populateFinishOrderGrid();
        }
         
        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void cbAutoSaveParticipantsGrid_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void participantsGrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void finishOrderGrid_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }


    } // End of class

}
